package com.cslg.ac.demo.dao.pet;

import com.cslg.ac.demo.dao.pet.po.PetAdoptPO;
import org.apache.ibatis.annotations.*;
import org.springframework.beans.FatalBeanException;

import java.util.List;

@Mapper
public interface PetAdoptDAO {

    String TABLE_NAME = "e_pet_adopt";
    String TABLE_LONG_AND_LAT = "e_user_long_lat";
    String INSERT_FIELD = "(id,source,immune,description,demand)";
    String SELECT_FIELD = "id,source,immune,description,video,demand";

    @Insert({"insert into",TABLE_NAME,INSERT_FIELD,"values(#{id},#{source},#{immune},#{description},#{demand})"})
    void insert(PetAdoptPO pet);

    @Update({"update ",TABLE_NAME,"set video = #{video} where id = #{id}"})
    void updateVideo(@Param("id")int id,@Param("video")String video);

    @Delete({"delete from ",TABLE_NAME,"where id = #{id}"})
    void delete(@Param("id")int id);


    @Select({"select ", SELECT_FIELD, "from ", TABLE_NAME, "where id = #{id}"})
    PetAdoptPO selectById(@Param("id")int id);


    @Select({"select distinct a.id from e_pet_adopt a,r_user_pet b,e_t_account_info c,e_p_account_info d,e_pet_common e where a.id = b.pet_id and a.id = e.id " +
            "and  e.gender = #{gender} and ((b.user_id = c.id and c.address like concat(concat('%',#{address}),'%'))) or (b.user_id = d.id and d.city like concat(concat('%',#{address}),'%'))))"})
    List<Integer> selectBySexAndAddr(@Param("gender")int gender, @Param("address")String address, @Param("offset") int offset, @Param("num") int num);

    @Select({"select distinct a.id from e_pet_adopt a,r_user_pet b,e_t_account_info c,e_p_account_info d,e_pet_common e where a.id = b.pet_id and a.id = e.id " +
            "and  e.type = #{type} and ((b.user_id = c.id and c.address like concat(concat('%',#{address}),'%'))) or (b.user_id = d.id and d.city like concat(concat('%',#{address}),'%'))))"})
    List<Integer> selectByTypeAndAddr(@Param("type")String type, @Param("address")String address, @Param("offset") int offset, @Param("num") int num);

    @Select({"select user_id userId,(power(mod(abs(longitude - #{longitude}),360),2) + power(abs(latitude - #{latitude}),2)) as distance from", TABLE_LONG_AND_LAT, "where longitude != '1000' and latitude != '1000' order by" +
            " distance limit #{offset}, #{num} "})
    List<Integer> userSortByLatAndLong(@Param("longitude") double longitude, @Param("latitude") double latitude, @Param("offset") int offset, @Param("num") int num);

    @Select({"select distinct a.id from e_pet_adopt a,r_user_pet b,e_t_account_info c,e_p_account_info d where a.id = b.pet_id and (b.user_id = c.id and (" +
            " c.address like concat(concat('%',#{address}),'%')) or (b.user_id = d.id and d.city like concat(concat('%',#{address}),'%')))"})
    List<Integer> selectByAdress(@Param("address")String address, @Param("offset") int offset, @Param("num") int num);

    @Select({"select id from ", TABLE_NAME, " order by id desc limit #{offset}, #{num} " })
    List<Integer> sortByTime(@Param("offset")int offset, @Param("num")int num);
}
